﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using System.Collections;
using DerptelligenceSite.Areas.Marein.Models.Blog;

namespace DerptelligenceSite.Areas.Marein.Models.Blog
{
    public static class BlogDatabase
    {
        private static String databasePrefix = "marein_blog_";
		private static int entriesPerPage = 10;

        private static bool Query(String queryString, out MySqlConnection conn, out MySqlDataReader rdr)
        {
            try
            {
                MySqlConnection Connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["connstring"].ConnectionString);

                Connection.Open();

                MySqlCommand mySqlCmd = Connection.CreateCommand();
                mySqlCmd.CommandText = queryString;
                mySqlCmd.CommandType = CommandType.Text;
                MySqlDataReader Reader = mySqlCmd.ExecuteReader();

                conn = Connection;
                rdr = Reader;
                return true;
            }
            catch
            {
                conn = null;
                rdr = null;
                return false;
            }
        }

		public static BlogEntry GetEntryByID( UInt32 id )
		{
			MySqlConnection conn;
			MySqlDataReader rdr;
			if ( !Query( "SELECT * FROM " + databasePrefix + "entries WHERE id = '" + id + "' LIMIT 0, 1", out conn, out rdr ) )
				return new BlogEntry (rdr);

			IEnumerator enumerator = rdr.GetEnumerator();

			BlogEntry entry = new BlogEntry { };

			while ( enumerator.MoveNext() )
			{
				entry = new BlogEntry( (IDataRecord)enumerator.Current );
			}
			conn.Dispose();

			return entry;
		}

		public static IEnumerable<BlogEntry> GetEntries ()
		{
			MySqlConnection conn;
			MySqlDataReader rdr;
			List<BlogEntry> entries = new List<BlogEntry>();
			if ( !Query( "SELECT * FROM " + databasePrefix + "entries WHERE public = '1' ORDER BY publishdate DESC LIMIT 0, " + entriesPerPage, out conn, out rdr ) )
				return new List<BlogEntry> { };

			while ( rdr.Read() )
			{
				entries.Add( new BlogEntry( rdr ) );
			}
			conn.Dispose();

			return entries;
		}

		public static IEnumerable<BlogEntry> GetEntriesByTopic( BlogTopic topic )
		{
			MySqlConnection conn;
			MySqlDataReader rdr;
			List<BlogEntry> entries = new List<BlogEntry>();
			if ( !Query( "SELECT * FROM " + databasePrefix + "entries WHERE topic = '" + topic.shortTitle + "' AND public = '1' ORDER BY publishdate DESC LIMIT 0, " + entriesPerPage, out conn, out rdr ) )
				return new List<BlogEntry> { };

			while ( rdr.Read() )
			{
				entries.Add( new BlogEntry( rdr ) );
			}
			conn.Dispose();

			return entries;
		}

		public static IEnumerable<BlogComment> GetCommentsByEntryID( UInt32 entryid )
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            List<BlogComment> comments = new List<BlogComment>();
            if ( !Query( "SELECT * FROM " + databasePrefix + "comments WHERE entryid = '" + entryid + "' ORDER BY publishdate DESC", out conn, out rdr ) )
                return new List<BlogComment> { };

			while ( rdr.Read() )
			{
				comments.Add( new BlogComment( rdr ) );
			}
			conn.Dispose();

            return comments;
        }

		public static UInt32 GetCommentCountByEntryID( UInt32 entryid )
		{
			UInt32 count = 0;

			MySqlConnection conn;
			MySqlDataReader rdr;
			if ( !Query( "SELECT *, COUNT(entryid) AS comments FROM " + databasePrefix + "comments WHERE entryid = '" + entryid + "' LIMIT 0, 1000", out conn, out rdr ) )
				return count;

			rdr.Read();
			count = Convert.ToUInt32( rdr["comments"] );
			
			conn.Dispose();

			return count;
		}

		public static List<BlogTopic> GetTopics( Boolean? includeUnlisted = false )
		{
			MySqlConnection conn;
			MySqlDataReader rdr;

			List<BlogTopic> topics = new List<BlogTopic>();

			if ( !Query( " SELECT * FROM " + databasePrefix + "topics " + ( (Boolean)includeUnlisted ? "" : "WHERE listed = '1'" ) + " ORDER BY ordering ASC", out conn, out rdr ) )
				return topics;

			if ( rdr.HasRows )
			{
				while ( rdr.Read() )
				{
					topics.Add( new BlogTopic( rdr ) );
				}
			}
			else
			{
				//error
			}

			conn.Dispose();

			return topics;
		}

        public static BlogTopic GetTopicByShortTitle( String shortTitle )
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            if ( !Query( "SELECT * FROM " + databasePrefix + "topics WHERE shortTitle = '" + shortTitle + "' LIMIT 0, 1", out conn, out rdr ) )
				return new BlogTopic { };

			IEnumerator enumerator = rdr.GetEnumerator();

            BlogTopic topic = new BlogTopic { };

			if ( rdr.HasRows )
			{
				while ( enumerator.MoveNext() )
				{
					topic = new BlogTopic( (IDataRecord)enumerator.Current );
				}
			} else {
				//error
			}

            conn.Dispose();

            return topic;
        }

        public static BlogStaticContent GetStaticContentByName( String name )
        {
            MySqlConnection conn;
            MySqlDataReader rdr;
            if ( !Query( "SELECT * FROM " + databasePrefix + "staticcontent WHERE name = '" + name + "' LIMIT 0, 1", out conn, out rdr ) )
				return new BlogStaticContent { };

			BlogStaticContent staticContent = new BlogStaticContent { };

			if ( rdr.HasRows )
			{
				rdr.Read();
				staticContent.id = (UInt32)rdr["id"];
				staticContent.name = (String)rdr["name"];
				staticContent.content = (String)rdr["content"];
			} else {
				//error
			}

			conn.Dispose();

			return staticContent;
        }
    }
}